﻿Public Class DRONTIME
    Private DONTID As Integer = Nothing
    Private PROVIDER As Integer = Nothing
    Private ONDAY As Integer = Nothing
    Private STATIME As String = Nothing
    Private FINTIME As String = Nothing
    Private NCASE As Integer = Nothing
    Private CYCTIME As Integer = Nothing
    Private STTDATE As String = Nothing
    Private EXPDATE As String = Nothing
    Private STATUS = Nothing
    Private SelectDrontime = Nothing
    Dim connect As CONDB = CONDB.NewConnection()

    Property DONTID_() As Integer
        Get
            Return DONTID
        End Get
        Set(value As Integer)
            DONTID = value
        End Set
    End Property
    Property PROVIDER_() As Integer
        Get
            Return PROVIDER
        End Get
        Set(value As Integer)
            PROVIDER = value
        End Set
    End Property
    Property ONDAY_() As Integer
        Get
            Return ONDAY
        End Get
        Set(value As Integer)
            ONDAY = value
        End Set
    End Property
    Property STATIME_() As String
        Get
            Return STATIME
        End Get
        Set(value As String)
            STATIME = value
        End Set
    End Property
    Property FINTIME_() As String
        Get
            Return FINTIME
        End Get
        Set(value As String)
            FINTIME = value
        End Set
    End Property
    Property NCASE_() As Integer
        Get
            Return NCASE
        End Get
        Set(value As Integer)
            NCASE = value
        End Set
    End Property
    Property CYCTIME_() As Integer
        Get
            Return CYCTIME
        End Get
        Set(value As Integer)
            CYCTIME = value
        End Set
    End Property
    Property STTDATE_() As String
        Get
            Return STTDATE
        End Get
        Set(value As String)
            STTDATE = value
        End Set
    End Property

    Property EXPDATE_() As String
        Get
            Return EXPDATE
        End Get
        Set(value As String)
            EXPDATE = value
        End Set
    End Property
    Property STATUS_() As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property
    Property SelectDrontime_() As String
        Get
            SelectDrontime = "SELECT "
            SelectDrontime += "`dontid`, "
            SelectDrontime += "h.`empid` AS 'รหัสผู้ให้บริการ', "
            SelectDrontime += "CONCAT_WS(' ', mpn.`ftprename`, h.`name`, h.`lname`) AS 'ชื่อผู้ให้บริการ', "
            SelectDrontime += "`onday`, "
            SelectDrontime += "`statime` AS 'เวลาเริ่มออกตรวจ', "
            SelectDrontime += "`fintime` AS 'เวลาสิ้นสุดการตรวจ', "
            SelectDrontime += "`ncase` AS 'จำนวนรับนัดต่อวัน', "
            SelectDrontime += "`cyctime` AS 'จำนวนเวลาต่อราย', "
            SelectDrontime += "drontime.`status` AS 'สถานะการเปิดใช้งาน', "
            SelectDrontime += "`st_date` AS 'วันที่เริ่มใช้', "
            SelectDrontime += "`fn_date` AS 'วันที่เลิกใช้', "
            SelectDrontime += "CASE WHEN(onday =  1) THEN 'อาทิตย์' "
            SelectDrontime += "ELSE CASE WHEN(onday = 2) THEN 'จันทร์' "
            SelectDrontime += "ELSE CASE WHEN(onday = 3) THEN 'อังคาร' "
            SelectDrontime += "ELSE CASE WHEN(onday = 4) THEN 'พุธ' "
            SelectDrontime += "ELSE CASE WHEN(onday = 5) THEN 'พฤหัสบดี' "
            SelectDrontime += "ELSE CASE WHEN(onday = 6) THEN 'ศุกร์' "
            SelectDrontime += "ELSE CASE WHEN(onday = 7) THEN 'เสาร์' "
            SelectDrontime += "ELSE 'ไม่กำหนดวัน' "
            SelectDrontime += "END "
            SelectDrontime += "END "
            SelectDrontime += "END "
            SelectDrontime += "END "
            SelectDrontime += "END "
            SelectDrontime += "END "
            SelectDrontime += "END AS 'วันที่ออกตรวจ' "
            SelectDrontime += "FROM drontime "
            SelectDrontime += "LEFT JOIN (SELECT `empid`, `prename`, `name`, `lname`, `nname` FROM hospemp WHERE `status` = 1 AND `f_dr` = '1') AS h ON drontime.`provider` = h.`empid` "
            SelectDrontime += "LEFT JOIN masprename mpn ON h.prename = mpn.prename "

            'SelectDrontime = "SELECT `dontid`,provider.`provider` as 'เลขที่ผู้ให้บริการ',`onday`,`statime` as 'เวลาเริ่มออกตรวจ',`fintime` as 'เวลาสิ้นสุดการตรวจ',`ncase` as 'จำนวนรับนัดต่อวัน',`cyctime` as 'จำนวนเวลาต่อราย',drontime.`status` as 'สถานะการเปิดใช้งาน',`st_date` as 'วันที่เริ่มใช้',`fn_date` as 'วันที่เลิกใช้' "
            'SelectDrontime += ",CASE WHEN(onday =  1) THEN 'วันอาทิตย์'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 2) THEN 'วันจันทร์'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 3) THEN 'วันอังคาร'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 4) THEN 'วันพุธ'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 5) THEN 'วันพฤหัสบดี'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 6) THEN 'วันศุกร์'"
            'SelectDrontime += "ELSE CASE WHEN(onday = 7) THEN 'วันเสาร์'"
            'SelectDrontime += "ELSE 'วันดี' END END END END END END END AS 'วันที่ออกตรวจ'"
            'SelectDrontime += " FROM drontime LEFT JOIN (SELECT `provider` FROM provider WHERE `status` = 1) as provider on drontime.`provider` = provider.`provider`"
            Return SelectDrontime
        End Get
        Set(value As String)

        End Set
    End Property
    Public Sub insertDrontimeData()
        'If connect.GetTable("SELECT `TMPID` FROM RGTONBILL WHERE `TMPID` = '" & TMPID & "'").Rows.Count > 0 Then
        '    MsgBox("มีข้อมูลส่วนนี้อยู่ในระบบแล้ว", MsgBoxStyle.Critical)
        'Else
        Try
            connect = CONDB.NewConnection()
            connect.BeginTrans()
            'connectlocal = conlocal.NewConnection()
            Dim Sql As String
            Sql = "INSERT INTO drontime "
            Sql += "(`provider`,`onday`,`statime`,`fintime`,`ncase`,`cyctime`,`status`) "
            Sql += " VALUES ("
            If PROVIDER = 0 Then
                Sql += "Null,"
            Else
                Sql += "'" & PROVIDER & "',"
            End If
            Sql += "'" & ONDAY & "',"
            Sql += "'" & STATIME & "',"
            Sql += "'" & FINTIME & "',"
            Sql += "'" & NCASE & "',"
            Sql += "'" & CYCTIME & "',"
            Sql += "'" & STATUS & "'"
            connect.ExecuteNonQuery(Sql)
            connect.CommitTrans()
            MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
        Finally
            connect.Dispose()
        End Try
        'End If
    End Sub
    Public Sub editDrontimedata()
        connect = CONDB.NewConnection()
        connect.BeginTrans()
        Try
            'connectlocal = conlocal.NewConnection()
            Dim sql As String
            sql = "UPDATE drontime SET `status` = '" & STATUS & "',"
            'If PROVIDER = 0 Then
            '    sql += " `provider` = Null ,"
            'Else
            '    sql += " `provider` = '" & PROVIDER & "',"
            'End If
            'sql += "`onday` = '" & ONDAY & "',"
            sql += "`statime` = '" & STATIME & "', "
            sql += "`fintime` = '" & FINTIME & "', "
            sql += "`st_date` = '" & STTDATE & "', "
            sql += "`fn_date` = '" & EXPDATE & "', "

            If NCASE = 0 Then
                sql += "`ncase` = null, "
            Else
                sql += "`ncase` = '" & NCASE & "', "
            End If

            If NCASE = 0 Then
                sql += "`cyctime` = null "
            Else
                sql += "`cyctime` = '" & CYCTIME & "' "
            End If

            sql += "WHERE `dontid` = '" & DONTID & "'"
            connect.ExecuteNonQuery(sql)
            connect.CommitTrans()
            MsgBox("ท่านได้ทำการแก้ไขตารางออกตรวจแพทย์เรียบร้อยแล้ว", MsgBoxStyle.Information)
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
        Finally
            connect.Dispose()
        End Try
    End Sub

End Class
